package itsm.isperp.module.service.db;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.dialect.DialectFactory;
import itsm.isperp.framework.data.domain.DataRequest;
import itsm.isperp.framework.data.excel.ExcelData;
import itsm.isperp.framework.data.excel.ExcelImportExecutor;
import itsm.isperp.framework.data.excel.ImportCellDesc;
import itsm.isperp.framework.web.request.JqGridFilterRuleOp;
import itsm.isperp.framework.web.request.JqGridRequest;
import itsm.isperp.framework.web.response.ResultMessage;
import itsm.isperp.module.dto.db.ExcelColumnToEntityField;
import itsm.isperp.module.entity.db.DbEntity;
import itsm.isperp.module.entity.db.DbEntityField;
import itsm.isperp.module.repository.db.DbEntityFieldMapper;
import itsm.isperp.module.repository.db.DbEntityMapper;
import itsm.isperp.module.service.configuration.ConfCategoryPropertyService;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jdbc.support.DatabaseType;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.test.context.transaction.AfterTransaction;

import com.fr.third.org.hsqldb.lib.StringUtil;

/**
 * 数据导入
 * 
 * @author zhg
 * 
 */
@Service
public class DbImpExpService {

  @Autowired
  protected DbEntityFieldMapper dbEntityFieldMapper;

  @Autowired
  protected SqlSessionFactory sqlSessionFactory;

  @Autowired
  protected DbEntityMapper dbEntityMapper;

  @Autowired
  protected ConfCategoryPropertyService confCategoryPropertyService;

  /**
   * 返回Excel文件的sheet名和列名信息
   * 
   * @para1 entityName 实体名称
   * @para2 filePath 文件路径
   * @return Map<String, List<ExcelColumn>>
   * @throws IOException
   * @throws FileNotFoundException
   * 
   */
  public Map<String, List<ExcelColumnToEntityField>> getExcelFileInfo(
      String entityName, String filePath) throws FileNotFoundException,
      IOException {

    // 获取实体对应的属性集合
    List<DbEntityField> dbEntityFileds = new ArrayList<DbEntityField>();
    dbEntityFileds = dbEntityFieldMapper.findAllByEntityName(entityName);

    Map<String, List<ExcelColumnToEntityField>> list = new LinkedHashMap<String, List<ExcelColumnToEntityField>>();
    HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));
    HSSFSheet sheet = null;
    for (int i = 0; i < wookbook.getNumberOfSheets(); i++) {
      String sheetName = wookbook.getSheetName(i);
      sheet = wookbook.getSheetAt(i);
      HSSFRow row = sheet.getRow(sheet.getFirstRowNum());
      // 读取首行的列头信息
      List<ExcelColumnToEntityField> columns = new ArrayList<ExcelColumnToEntityField>();
      for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
        ExcelColumnToEntityField tmp = new ExcelColumnToEntityField();
        if(row.getCell(j)==null ||row==null)
          continue; 
        String cellValue = row.getCell(j).getStringCellValue();
        if (StringUtils.isNotEmpty(cellValue)) {
          tmp.setExcelColumn(cellValue);
          tmp.setEntityField(getRelationName(dbEntityFileds,
              tmp.getExcelColumn()));
          columns.add(tmp);
        }

      }
      list.put(sheetName, columns);
    }
    return list;
  }

  /**
   * 找到excel列名对应的实体的属性
   * 
   * @param entityName
   * @return
   */
  private String getRelationName(List<DbEntityField> fields,
      String excelColumnName) {
    String strReturn = "";
    for (DbEntityField v : fields) {
      if (StringUtils.isNotEmpty(excelColumnName)
          && excelColumnName.indexOf(v.getName()) >= 0)
        strReturn = v.getName();
    }
    return strReturn;
  }

  /**
   * 验证数据项匹配之后的关系是否满足数据导入要求
   * 
   * @para1 entityName 实体名称
   * @para2 mitchedList
   * @return
   */
  public ResultMessage isValidOfExcelFile(String entityName,
      List<ExcelColumnToEntityField> mitchedList) {
    boolean blReturn = true;
    String msg = "";
    List<DbEntityField> fields = dbEntityFieldMapper
        .findAllByEntityName(entityName);
    for (DbEntityField field : fields) {
      if (field.isNotnull()) // 对于不能为空的字段必须有对应的导入字段匹配
      {
        boolean blMatch = false;
        for (ExcelColumnToEntityField excelColumnToEntityField : mitchedList) {
          if (excelColumnToEntityField.getEntityField() == field
              .getName()) {
            blMatch = true;
            break;
          }
        }
        if (!blMatch) {
          blReturn = false;
          msg = msg + field.getName() + "对应的导入项不能为空！\n\r";
        }
      }
    }
    return ResultMessage.newInstance(blReturn, msg);

  }

  /**
   * 生成Excel导入模板
   * 
   * @param entityName
   * @param entityCategoryId
   * @return
   * @throws IOException
   */
  public String generateImportExcelModel(String entityName,
      String entityCategoryId, String entityCategoryName)
      throws IOException {

    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    String s = sdf.format(DateTime.now().toDate());

    String fileName = "";
    String title = "";
    if (StringUtils.isNotEmpty(entityCategoryName)) {
      fileName = entityName + "_" + entityCategoryName + "模板" + s
          + ".xls";
      title = entityCategoryName;
    } else {
      fileName = entityName + "模板" + s + ".xls";
      title = entityName;
    }

    String filePath = ContextHolder.getUploadFileStorePath() + "/"
        + fileName;

    // 获取实体对象
    DbEntity entity = null;
    DataRequest req = new JqGridRequest();
    req.addFilterRule("name", JqGridFilterRuleOp.eq, entityName); // 名称
    List<DbEntity> dbentityList = dbEntityMapper.findAllByRequest(req)
        .getContent();
    if (dbentityList != null && dbentityList.size() > 0) {
      entity = dbentityList.get(0);
    }
    if (entity == null) // 如果实体为空则退出导入
    {
      return null;
    }

    List<DbEntityField> fields = dbEntityFieldMapper
        .findAllByEntityName(entityName);
    // 如果是资产，则需要将其自定义属性加入到模板列中
    if (entity.getEntityType().equalsIgnoreCase("Customer")) {
      if (entity.getTableName().equalsIgnoreCase("conf_ci")) {
        List<Map<String, Object>> properties = confCategoryPropertyService
            .findAllProperty(entityCategoryId);
        for (int j = 0; j < properties.size(); j++) {
          Map<String, Object> property = properties.get(j);
          fields.add(cloneDbEntityField(property));

        }
      }
    }
    File file = new File(filePath);
    file.createNewFile();
    FileOutputStream out = new FileOutputStream(filePath);
    exportExcelModel(title, fields, out);
    out.close();

    return ContextHolder.getUploadFileStore() + "/" + fileName;
  }

  /**
   * 根据自定义属性构造数据属性实体
   * 
   * @param property
   * @return
   */
  private DbEntityField cloneDbEntityField(Map<String, Object> property) {
    DbEntityField obj = new DbEntityField();
    obj.setColumnName(property.get("name").toString());
    obj.setName(property.get("name").toString());
    obj.setFieldType(property.get("data_type").toString());

    if (property.get("validate_exp") != null) {
      obj.setValidateExp(property.get("validate_exp").toString());
    }
    if (property.get("validate_desc") != null) {
      obj.setValidateDesc(property.get("validate_desc").toString());
    }
    boolean isnotnull = false;
    if (property.get("notnull") != null
        && property.get("notnull").toString().equalsIgnoreCase("1")) {
      isnotnull = true;
    }
    obj.setUseForImport(true);
    obj.setNotnull(isnotnull);
    return obj;
  }

  /**
   * 生成Excel模板，输出到指定IO设备上
   * 
   * @param title
   *            表格标题名
   * @param fields
   *            表格属性列名数组
   * @param out
   *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
   * @throws IOException
   */
  public void exportExcelModel(String title, List<DbEntityField> fields,
      OutputStream out) throws IOException {
    // 声明一个工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 生成一个表格
    HSSFSheet sheet = workbook.createSheet(title);
    // 设置表格默认列宽度为15个字节
    sheet.setDefaultColumnWidth(15);

    // 生成一个样式
    HSSFCellStyle style = workbook.createCellStyle();
    // 设置这些样式
    style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    // 生成一个字体
    HSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.VIOLET.index);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    // 把字体应用到当前的样式
    style.setFont(font);

    // 产生表格标题行
    HSSFRow row = sheet.createRow(0);
    HSSFPatriarch p = sheet.createDrawingPatriarch();
    int column = 0;
    for (int i = 0; i < fields.size(); i++) {
      DbEntityField field = fields.get(i);
      if (field.isUseForImport()) {
        // 给列头赋值
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        HSSFRichTextString text = new HSSFRichTextString(
            field.getName());
        cell.setCellValue(text);

        // 定义每一列批注(如果有格式说明)
        if (!StringUtil.isEmpty(field.getValidateDesc())) {
          HSSFComment comment = p.createComment(new HSSFClientAnchor(
              0, 0, 0, 0, (short) 3, 3, (short) 4, 8));
          comment.setString(new HSSFRichTextString(field
              .getValidateDesc()));
          cell.setCellComment(comment);
        }

        // CellRangeAddressList region=new CellRangeAddressList();
        // region.addCellRangeAddress(1, column, 65536, column);
        // DataValidationConstraint
        // dvConstraint=DVConstraint.createCustomFormulaConstraint(null)
        // ;
        // HSSFDataValidation dataValidation=new
        // HSSFDataValidation(region,dvConstraint);
        //

        HSSFDataFormat format = workbook.createDataFormat();

        HSSFCellStyle style1 = workbook.createCellStyle();
        style1.setDataFormat(format.getFormat("@"));
        sheet.setDefaultColumnStyle(i, style1);
        sheet.setColumnWidth(i, 15 * 256);

        // switch (field.getFieldType()) {
        // case "string":
        // case "datetime":
        // case "date":
        //
        // break;
        // case "number":
        // style1.setDataFormat((short) Cell.CELL_TYPE_NUMERIC);
        // break;
        // default:
        // style1.setDataFormat((short) Cell.CELL_TYPE_STRING);
        // break;
        // }

      }

    }

    workbook.write(out);

  }

  /**
   * 导入数据
   * 
   * @param filePath
   *            文件路径
   * @param sheetName
   *            sheet名称
   * @param importType
   *            导入类型 0：插入 1：更新
   * @param entityName
   *            要导入的数据实体
   * @param mitchedList
   *            数据实体与Excel列头的对应关系
   * @param entityCategoryId
   *            带自定义属性的数据实体对应分类的ID
   * @return 导入结构
   * @throws SQLException
   */
  public ResultMessage importExcelData(String filePath, String sheetName,
      String importType, String entityName,
      List<ExcelColumnToEntityField> mitchedList,
      String entityCategoryId, String entityCategoryName) {
    try {
      ResultMessage resultMessage = null;

      // 获取实体对象
      DbEntity entity = null;
      DataRequest req = new JqGridRequest();
      req.addFilterRule("name", JqGridFilterRuleOp.eq, entityName); // 名称
      List<DbEntity> dbentityList = dbEntityMapper.findAllByRequest(req)
          .getContent();
      if (dbentityList != null && dbentityList.size() > 0) {
        entity = dbentityList.get(0);
      }
      if (entity == null) {// 如果实体为空则退出导入
        return null;
      }

      // 获取Excel列头与数据实体的对应关系
      Map<String, DbEntityField> map = new HashMap<String, DbEntityField>();
      List<DbEntityField> fields = dbEntityFieldMapper
          .findAllByEntityName(entityName);

      List<DbEntityField> fieldsTemp = new ArrayList<DbEntityField>();
      int importColumnSize=0;
      for (DbEntityField obj : fields) {
        fieldsTemp.add(obj);
        if(obj.isUseForImport())
        {
          importColumnSize+=1;
        }
      }
      //处理扩展属性列，如果Excel表数据中所有都是同类别，则会检查导入扩展属性

      if (entity.getEntityType().equalsIgnoreCase("Customer")) {
        if (entity.getTableName().equalsIgnoreCase("conf_ci") ) {
          String categoryId=ExcelImportExecutor.checkIfContainCategoryPy(importColumnSize, filePath, sheetName);
          entityCategoryId=categoryId;
          if(StringUtils.isNotEmpty(categoryId))
                    {
                      List<Map<String, Object>> properties = confCategoryPropertyService
                  .findAllProperty(categoryId);
              for (int j = 0; j < properties.size(); j++) {
                Map<String, Object> property = properties.get(j);
                DbEntityField field=cloneDbEntityField(property);
                fieldsTemp.add(field);
                map.put(property.get("name").toString(),field);
              }
                    }
          
        }
      }

      for (ExcelColumnToEntityField excelColumnToEntityField : mitchedList) {
        map.put(excelColumnToEntityField.getExcelColumn(),
            getDbEntityField(excelColumnToEntityField, fieldsTemp));
      }

      ExcelData excelData = ExcelImportExecutor.readExcel(map, filePath,
          sheetName);

      if (CollectionUtils.isEmpty(excelData.getRepeatData())) {
        return ResultMessage.newInstance(false, "您的导入文件没有任何数据，请重新上传！");
      }

      // 如果返回数据验证出错，则返回失败，并返回错误信息，否则执行导入
      String result = excelData.getErrorMsg();
      if (StringUtils.isNotEmpty(result)) {
        return ResultMessage.newInstance(false, result);
      } else {

        List<Map<Integer, DbEntityField>> paras = new ArrayList<Map<Integer, DbEntityField>>();
        List<Map<Integer, String>> cusParas = new ArrayList<Map<Integer, String>>();
        String strSql = "";
        strSql = prepareSqlStatement(importType, dbentityList.get(0),
            fields, paras, cusParas, entityCategoryId);
        // 执行SQL语句
        resultMessage = excuteSqlStatement(importType, strSql,
            excelData, dbentityList.get(0), paras, cusParas,
            entityCategoryId, entityCategoryName);
        if (entity.getTableName().equalsIgnoreCase("conf_ci") ) {
            String sql="UPDATE conf_ci a SET conf_category_id=(SELECT id FROM conf_category WHERE NAME=a.`conf_category_name` LIMIT 1)"
                  +",cus_customer_id=(SELECT id FROM cus_customer WHERE NAME=a.`cus_customer_name` LIMIT 1) "
                  +",use_staff_id=(SELECT id FROM app_user WHERE fullname=a.use_staff_name LIMIT 1)"
                  +",management_staff_id=(SELECT id FROM app_user WHERE fullname=a.management_staff_name LIMIT 1)"
                  +",support_staff_id=(SELECT id FROM app_user WHERE fullname=a.support_staff_name LIMIT 1)"
                  +",location_id=(SELECT id FROM conf_location WHERE NAME=a.location_name LIMIT 1)"
                  +" where conf_category_id is null";
            JdbcTemplate jdbcTemplate = ContextHolder 
              .getSpringBean("jdbcTemplate");
            jdbcTemplate.execute(sql);
        }
        if (entity.getTableName().equalsIgnoreCase("icd_incident") ) {
            String sql="UPDATE icd_incident a SET cus_user_id=(SELECT id FROM app_user WHERE fullname=a.`cus_user_id` LIMIT 1)"
                  +",icd_category_id=(SELECT id FROM icd_category WHERE NAME=a.`icd_category_name` LIMIT 1) "
                  +",supporter_id=(SELECT id FROM app_user WHERE fullname=a.supporter_name LIMIT 1)"
                  +",solver_id=(SELECT id FROM app_user WHERE fullname=a.supporter_name LIMIT 1)"
                  +",project_id=(SELECT id FROM proj_project WHERE name=a.project_id LIMIT 1)"
                  +",solver_name=supporter_name,service_desk_solved=0,creator_name=supporter_name,creator='admin',occurrence_frequency='首次',deal_type='2'"
                  +" where creator is null";
            JdbcTemplate jdbcTemplate = ContextHolder 
              .getSpringBean("jdbcTemplate");
            jdbcTemplate.execute(sql);
        }
        if (entity.getTableName().equalsIgnoreCase("spt_sparepart") ) {
          String sql="UPDATE spt_sparepart set id=code where creator is null";
          JdbcTemplate jdbcTemplate = ContextHolder 
            .getSpringBean("jdbcTemplate");
          jdbcTemplate.execute(sql);
      }
        
        if (entity.getTableName().equalsIgnoreCase("app_user") ) {
          String sql="UPDATE app_user set password='WP5Essdoi1Q=' where password is null";
          JdbcTemplate jdbcTemplate = ContextHolder 
            .getSpringBean("jdbcTemplate");
          jdbcTemplate.execute(sql);
      }

        return resultMessage;

      }
    } catch (Exception e) {
      return ResultMessage.newInstance(false, e.toString());
    }

  }

  /**
   * 准备导入语句
   * 
   * @param importType
   *            导入类型：0:新增 1：更新
   * @param dbEntity
   *            数据实体
   * @param fields
   *            数据实体属性
   * @param paras
   *            参数表<参数位置，参数名称>
   * @param cusParas
   *            自定义参数表<参数位置，参数名称>
   * @return
   */
  private String prepareSqlStatement(String importType, DbEntity dbEntity,
      List<DbEntityField> fields,
      List<Map<Integer, DbEntityField>> paras,
      List<Map<Integer, String>> cusParas, String dbEntityCategoryId) {

    // 1、准备插入语句,记住参数的位置以及对应的列Map
    String strSql = "";
    switch (dbEntity.getEntityType()) {
    case "List": // 普通数据实体
      if (importType.equals("0")) {

        strSql = getListInsertSql(dbEntity, fields, paras);// 新增
      } else {
        strSql = getListUpdateSql(dbEntity, fields, paras); // 更新
      }

      break;
    case "Tree": // 树形结构数据实体
      if (importType.equals("0")) // 新增
      {

        strSql = getListInsertSql(dbEntity, fields, paras);
      } else // 更新
      {
        strSql = getListUpdateSql(dbEntity, fields, paras);
      }

      break;
    case "Customer": // 带有自定义属性实体，主要是资产，资产的自定义属性
      if (importType.equals("0")) // 新增
      {
        strSql = getCustomFieldInsertSql(dbEntity, dbEntityCategoryId,
            fields, paras, cusParas);
      } else // 更新
      {
        // todo
        strSql = getCustomFieldUpdateSql(dbEntity, dbEntityCategoryId,
            fields, paras, cusParas);
      }

      break;
    default:
      break;

    }
    return strSql;

  }

  /**
   * 获取清单型数据实体的导入SQL(新增 )
   * 
   * @param dbEntity
   *            数据实体
   * @param fields
   *            数据实体属性级
   * @param paras
   *            参数列表（在本函数添加，需要返回给调用者使用）
   * @return
   */
  private String getListInsertSql(DbEntity dbEntity,
      List<DbEntityField> fields, List<Map<Integer, DbEntityField>> paras) {
    String strSql = "";
    String column = "";
    String values = "";
    Integer position = 1; // 参数的位置
    Map<Integer, DbEntityField> para = new HashMap<Integer, DbEntityField>();
    for (DbEntityField field : fields) {
      if (field.isUseForImport() || field.getName().equals("id")) {
        column = column + field.getColumnName() + ",";
        if (StringUtils.isNotEmpty(field.getSqlStr())) {
          values = values + field.getSqlStr() + ",";
          if (field.getSqlStr().indexOf("?") >= 0) {
            para.put(position++, field);
          }
        } else {
          values = values + "?,";
          para.put(position++, field);
        }

      } else {
        // 非导入字段的数据赋值：存放于属性中
        /*if (StringUtils.isNotEmpty(field.getSqlStr())) {
          values = values + field.getSqlStr() + ",";
        } else {
          values = values + "null,";
        }*/
      }
    }
    if (column.endsWith(",")) {
      column = column.substring(0, column.length() - 1);
    }
    if (values.endsWith(",")) {
      values = values.substring(0, values.length() - 1);
    }
    strSql = "insert into " + dbEntity.getTableName() + "(" + column
        + ") values(" + values + ")";

    paras.add(para);
    return strSql;
  }

  /**
   * 获取清单型数据实体的导入SQL（修改 ）
   * 
   * @param dbEntity
   *            数据实体
   * @param fields
   *            数据实体属性级
   * @param paras
   *            参数列表（在本函数添加，需要返回给调用者使用）
   * @return
   */
  private String getListUpdateSql(DbEntity dbEntity,
      List<DbEntityField> fields, List<Map<Integer, DbEntityField>> paras) {

    // 更新逻辑：先按关键字删除数据，然后插入
    // 构建删除脚本
    String delSql = "";
    for (DbEntityField field : fields) {
      if (field.isUniqueKey()
          && !field.getColumnName().equalsIgnoreCase("id")) {
        delSql = " and " + field.getColumnName() + "=?";

        delSql = "delete from " + dbEntity.getTableName()
            + " where 1=1" + delSql + ";";

        Map<Integer, DbEntityField> para = new HashMap<Integer, DbEntityField>();
        para.put(1, field);
        paras.add(para); // 删除主表的参数表

      }
    }
    String insertSql = this.getListInsertSql(dbEntity, fields, paras);

    return delSql + insertSql;
  }

  /**
   * 获取带自定义属性数据实体的导入SQL(新增)
   * 
   * @param dbEntity
   *            数据实体
   * @Param dbEntityCategoryId 分类Id（自定义数据实体通常根据其分类获取其自定义属性集）
   * @param fields
   *            数据实体属性级
   * @param paras
   *            参数列表（在本函数添加，需要返回给调用者使用）
   * @return
   */
  private String getCustomFieldInsertSql(DbEntity dbEntity,
      String dbEntityCategoryId, List<DbEntityField> fields,
      List<Map<Integer, DbEntityField>> paras,
      List<Map<Integer, String>> cusParas) {
    String strSql = "";
    String column = "";
    String values = "";
    Integer position = 1; // 参数的位置

    Map<Integer, DbEntityField> para = new HashMap<Integer, DbEntityField>();
    for (DbEntityField field : fields) {
      
      if (field.isUseForImport()
          || field.getColumnName().equalsIgnoreCase("id")) {
        column = column + field.getColumnName() + ",";
        if (StringUtils.isNotEmpty(field.getSqlStr())) {
          values = values + field.getSqlStr() + ",";
          if (field.getSqlStr().indexOf("?") >= 0) {
            para.put(position++, field);
          }
        } else {
          values = values + "?,";
          para.put(position++, field);
        }

      } else { 
        // 非导入字段的数据赋值：存放于属性中
        /*if (StringUtils.isNotEmpty(field.getSqlStr())) {
          values = values + field.getSqlStr() + ",";
        } else {
          values = values + "null,";
        }*/
      }
    }
    if (column.endsWith(",")) {
      column = column.substring(0, column.length() - 1);
    }
    if (values.endsWith(",")) {
      values = values.substring(0, values.length() - 1);
    }
    strSql = "insert into " + dbEntity.getTableName() + "(" + column
        + ") values(" + values + ")";

    paras.add(para);

    // 自定义属性SQL插入
    String customFiledSql = "";
    if (dbEntity.getTableName().equalsIgnoreCase("conf_ci")) {
      // 资产导入
      List<Map<String, Object>> properties = confCategoryPropertyService
          .findAllProperty(dbEntityCategoryId);
      for (int j = 0; j < properties.size(); j++) {
        customFiledSql = customFiledSql
            + ";insert into conf_ci_property(id,value,conf_ci_id,conf_category_property_id,modifier,modify_date)";
        Map<String, Object> property = properties.get(j);
        String dateFunction = "";
        String guidFunction = "";
        if (DialectFactory.getDatabaseType() == DatabaseType.ORACLE) {
          dateFunction = "sysdate";
          guidFunction = "sys_guid()";
        } else {
          dateFunction = "sysdate()";
          guidFunction = "uuid()";
        }

        customFiledSql = customFiledSql + "values(" + guidFunction
            + ",?,?,'" + property.get("id") + "','"
            + ContextHolder.getLoginUsername() + "',"
            + dateFunction + "" + ")";

        Map<Integer, String> cusPara = new HashMap<Integer, String>();
        cusPara.put(1, property.get("name").toString());
        cusPara.put(2, "conf_ci_id");
        cusParas.add(cusPara);
      }
    }

    return strSql + customFiledSql;
  }

  /**
   * 获取带自定义属性数据实体的导入SQL(更新)
   * 
   * @param dbEntity
   *            数据实体
   * @Param dbEntityCategoryId 分类Id（自定义数据实体通常根据其分类获取其自定义属性集）
   * @param fields
   *            数据实体属性级
   * @param paras
   *            参数列表（在本函数添加，需要返回给调用者使用）
   * @return
   */
  private String getCustomFieldUpdateSql(DbEntity dbEntity,
      String dbEntityCategoryId, List<DbEntityField> fields,
      List<Map<Integer, DbEntityField>> paras,
      List<Map<Integer, String>> cusParas) {

    // 更新逻辑：先按关键字删除数据，然后插入
    // 构建主表删除脚本
    // 构建从表删除脚本

    String delSql = "";
    String delDetailSql = "";
    String sqlWhere = "";
    for (DbEntityField field : fields) {
      if (field.getColumnName().equalsIgnoreCase("id")) { //此处改了，可能导致错误，请参阅历史
        sqlWhere = " and " + field.getColumnName() + "=?";

        if (dbEntity.getTableName().equalsIgnoreCase("conf_ci") && field.getColumnName().equalsIgnoreCase("code")) {
          Map<Integer, DbEntityField> para1 = new HashMap<Integer, DbEntityField>();
          para1.put(1, field);
          paras.add(para1); // 删除从表的参数表
          delDetailSql = "delete from conf_ci_property where conf_ci_id in (select id from conf_ci where 1=1"
              + sqlWhere + ");";
        }
        Map<Integer, DbEntityField> para = new HashMap<Integer, DbEntityField>();
        para.put(1, field);
        paras.add(para); // 删除主表的参数表
        delSql = "delete from " + dbEntity.getTableName()
            + " where 1=1" + sqlWhere + ";";
        break;
      }
    }

    // 插入脚本
    String insertSql = this.getCustomFieldInsertSql(dbEntity,
        dbEntityCategoryId, fields, paras, cusParas);

    return delDetailSql + delSql + insertSql;
  }

  /**
   * 执行数据导入
   * 
   * @param strSql
   * @param excelData
   * @param dbEntity
   * @param paras
   * @param cusParas
   * @throws SQLException
   */
  private ResultMessage excuteSqlStatement(String importType, String strSql,
      ExcelData excelData, DbEntity dbEntity,
      List<Map<Integer, DbEntityField>> paras,
      List<Map<Integer, String>> cusParas, String categoryId,
      String categoryName) throws SQLException {
    switch (dbEntity.getEntityType()) {
    case "List": // 普通数据实体
      return importListEntity(importType, strSql, excelData, dbEntity,
          paras);

    case "Tree": // 树形结构数据实体，插入之后，需要更新其父节点以及路径
      return importTreeEntity(importType, strSql, excelData, dbEntity,
          paras);

    case "Customer": // 带有自定义属性实体，主要是资产，资产的自定义属性
      return importCustomFieldEntity(importType, strSql, excelData,
          dbEntity, paras, cusParas, categoryId, categoryName);
    default:
      return ResultMessage.success();
    }
  }

  /**
   * 导入List类型的数据实体
   * 
   * @param strSql
   * @param excelData
   * @param paras
   * @throws SQLException
   */
  @AfterTransaction
  private ResultMessage importListEntity(String importType, String strSql,
      ExcelData excelData, DbEntity dbEntity,
      List<Map<Integer, DbEntityField>> paras) throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession(false);
    Connection con = sqlSession.getConnection();
    List<String> pkeyList=new ArrayList<String>();
    
    String tableName=dbEntity.getTableName();

    // 首先删除原来表中所有的数据
    if (importType.equalsIgnoreCase("0")) {
      String delSql = "delete from " + dbEntity.getTableName();
      Statement statement = con.createStatement();
      statement.execute(delSql);
      statement.close();
    }

    String[] sqlList = strSql.split(";");
    List<PreparedStatement> psts = new ArrayList<PreparedStatement>();
    for (int i = 0; i < sqlList.length; i++) {
      psts.add(con.prepareStatement(sqlList[i]));
    }

  // 2、开始导入
    List<Map<String, ImportCellDesc>> list = excelData.getRepeatData();
    String primaryKey="id";
    String primaryKeyValue="";
    String delSql="";
    for (int i = 0; i < list.size(); i++) {
      if(list.get(i).get("id")!=null)
      {
        if(list.get(i).get("id").getFieldValue()==null)
          continue;
        primaryKeyValue = list.get(i).get("id").getFieldValue().toString();
      }
      else if(list.get(i).get("id")==null && (list.get(i).get("name")!=null && tableName.equals("app_user")))
      {
        if(list.get(i).get("name").getFieldValue()==null)
          continue;
        primaryKey="name";
        primaryKeyValue = list.get(i).get("name").getFieldValue().toString();;
      }else if(list.get(i).get("id")==null && list.get(i).get("code")!=null)
      {
        if(list.get(i).get("code").getFieldValue()==null)
          continue;
        primaryKey="code";
        primaryKeyValue = list.get(i).get("code").getFieldValue().toString();
      }
      else if(list.get(i).get("id")==null && list.get(i).get("name")!=null)
      {
        if(list.get(i).get("name").getFieldValue()==null)
          continue;
        primaryKey="name";
        primaryKey = list.get(i).get("name").getFieldValue().toString();
      }
      pkeyList.add(primaryKey);
      /*20170704新增*/
      delSql = "delete from " + dbEntity.getTableName()+" where "+primaryKey+"='"+primaryKeyValue+"'";
      Statement statement = con.createStatement();
      statement.execute(delSql);
      statement.close();
      
      
      Map<String, ImportCellDesc> map = list.get(i);
      for (int j = 0; j < psts.size(); j++) {
        ResultMessage resultMessage = setPreparedStatementValue(
            psts.get(j), map, paras.get(j), primaryKey, "", "");
        if (!resultMessage.success) {
          return resultMessage;
        }
      }
      for (PreparedStatement pstnew : psts) {
        pstnew.addBatch();
      }

    }
    for (PreparedStatement pstnew : psts) {
      pstnew.executeBatch();
    }

    con.commit();

    return ResultMessage.success(pkeyList);

    // con.close();
    // sqlSession.close();

  }

  /**
   * 导入List类型的数据实体
   * 
   * @param strSql
   * @param excelData
   * @param paras
   * @throws SQLException
   */
  @AfterTransaction
  private ResultMessage importTreeEntity(String importType, String strSql,
      ExcelData excelData, DbEntity dbEntity,
      List<Map<Integer, DbEntityField>> paras) throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession(false);
    Connection con = sqlSession.getConnection();
    try {

      // 首先删除原来表中所有的数据
      if (importType.equalsIgnoreCase("0")) {
        String delSql = "delete from " + dbEntity.getTableName();
        Statement statement = con.createStatement();
        statement.execute(delSql);
        statement.close();
      }
      String[] sqlList = strSql.split(";");
      List<PreparedStatement> psts = new ArrayList<PreparedStatement>();

      for (int i = 0; i < sqlList.length; i++) {
        psts.add(con.prepareStatement(sqlList[i]));

      }

      // 2、开始导入
      List<Map<String, ImportCellDesc>> list = excelData.getRepeatData();
      for (int i = 0; i < list.size(); i++) {
        Map<String, ImportCellDesc> map = list.get(i);

        for (int j = 0; j < psts.size(); j++) {
          ResultMessage resultMessage = setPreparedStatementValue(
              psts.get(j), map, paras.get(j), "", "", "");
          if (!resultMessage.success) {
            return resultMessage;
          }
        }
        for (PreparedStatement pstnew : psts) {
          pstnew.addBatch();
        }

      }
      for (PreparedStatement pstnew : psts) {
        pstnew.executeBatch();
      }

      con.commit();

      // 批量导入后，需要更新ParentID
      String updateParentIdSql = "";
      if (DialectFactory.getDatabaseType() == DatabaseType.ORACLE) {
        updateParentIdSql = "update " + dbEntity.getTableName()
            + " SET parent_id=(select id from "
            + dbEntity.getTableName() + " B where B.name="
            + dbEntity.getTableName() + ".parent_id) ";
      } else {
        updateParentIdSql = "update " + dbEntity.getTableName()
            + " a join " + dbEntity.getTableName()
            + " b on b.name=a.parent_id SET a.parent_id=b.id";
      }

      Statement statement = con.createStatement();
      statement.execute(updateParentIdSql);
      statement.close();

      CallableStatement statBuildTree = con
          .prepareCall("{CALL sp_treenode_rebuild(?)}");
      statBuildTree.setString(1, dbEntity.getTableName());
      statBuildTree.executeUpdate();
      statBuildTree.close();

      return ResultMessage.success();

      // con.close();

      // sqlSession.close();
    } catch (Exception e) {
      throw e;
    } finally {
      // 关闭连接和Session
      // if (!con.isClosed()) {
      // con.close();
      // }
      // if (sqlSession != null) {
      // sqlSession.close();
      // }
    }
  }

  /**
   * 为SQLStatement赋值
   * 
   * @param pst
   *            SQLStatement
   * @param map
   *            Excel数据行 string：属性名，ImportCellDesc：单元格数据
   * @param paras
   *            参数表
   * @param primaryKey
   *            参数表 当导入带有自定义属性的数据时，主数据的Id以及从数据的外键需要赋值
   * @throws SQLException
   */
  private ResultMessage setPreparedStatementValue(PreparedStatement pst,
      Map<String, ImportCellDesc> map, Map<Integer, DbEntityField> paras,
      String primaryKey, String categoryId, String categoryName)
      throws SQLException {
    Set<Integer> set = paras.keySet();
    for (int key : set) {
      DbEntityField field = paras.get(key);
      if (field.getColumnName().equalsIgnoreCase("id")) {
        pst.setString(key, primaryKey);
      }  else {
        Object cellValue = null;
        try {
          cellValue = map.get(field.getColumnName()).getFieldValue();
        } catch (Exception e) {
          return ResultMessage
              .newInstance(
                  false,
                  "错误信息：数据项【"
                      + field.getName()
                      + "】对应的Excel模板列为空，请检查Excel模板列是否存在重名现象或者与数据项存在一对多情形！");
        }

        switch (field.getFieldType().toLowerCase()) {
        case "string":
          pst.setString(key, (String) cellValue);
          break;
        case "number":
          Integer tempNumber = 0;
          if (cellValue != null && !cellValue.toString().trim() .equals("")) {
            pst.setString(key, ((String) cellValue).trim());
          } else {
            pst.setString(key, tempNumber.toString());
          }

          break;
        case "date":
          Date dt = null;
          if (cellValue != null && !cellValue.toString().trim() .equals("")) {
            dt = new Date((StringToDate(cellValue.toString(),
                "yyyy-MM-dd")).getTime());
          }
          pst.setDate(key, dt);

          break;
        case "datetime":
          java.sql.Timestamp d = null;
          if (cellValue != null && !cellValue.toString().trim() .equals("")) {
            d = new  java.sql.Timestamp((StringToDateTime(cellValue.toString(),
                "yyyy-MM-dd HH:mm:ss")).getTime());
          }
          pst.setTimestamp(key, d);

          break;
        default:
          pst.setString(key, ((String) cellValue));
          break;
        }
      }

    }
    return ResultMessage.success();

  }

  public java.util.Date StringToDate(String time, String formatStr) {
    SimpleDateFormat formatter;

    time = time.trim();

    if (time.length() < 6) {
      formatter = new SimpleDateFormat("yyyy-MM-dd");
      time = formatter.format(
          DateUtil.getJavaDate(Double.parseDouble(time))).toString();
    } else if (time.indexOf("-") > -1) {
      formatter = new SimpleDateFormat("yyyy-MM-dd");

    } else if ((time.indexOf("/") > -1)) {
      formatter = new SimpleDateFormat("yyyy/MM/dd");
    } else if ((time.indexOf(".") > -1)) {
      formatter = new SimpleDateFormat("yyyy.MM.dd");
    }

    else {
      formatter = new SimpleDateFormat("yyyyMMdd");
    }

    java.util.Date ctime = null;
    try {
      ctime = formatter.parse(time);
    } catch (ParseException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

    return ctime;
  }
  
  public java.util.Date StringToDateTime(String time, String formatStr) {
    SimpleDateFormat formatter;
    //time = time.trim();
    if(StringUtils.isEmpty(time))
      return null;
    time=time.replace(".0", "").replace("  ", " ");
    if (time.length() < 6) {
      formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
      time = formatter.format(
          DateUtil.getJavaDate(Double.parseDouble(time))).toString();
    } else if (time.indexOf("-") > -1) {
      formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    } else if ((time.indexOf("/") > -1)) {
      formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    } else if ((time.indexOf(".") > -1)) {
      formatter = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
    }

    else {
      formatter = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
    }

    java.util.Date ctime = null;
    try {
      if(time.indexOf(":")<0)
        time=time+" 00:00:00";
      if(time.split(":").length==3)
      {
        
      }else if(time.split(":").length==2)
      {
        time=time+":00";
      }
      ctime = formatter.parse(time);
    } catch (ParseException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

    return ctime;
  }

  /**
   * 为SQLStatement赋值
   * 
   * @param pst
   *            SQLStatement
   * @param map
   *            Excel数据行 string：属性名，ImportCellDesc：单元格数据
   * @param paras
   *            参数表
   * @throws SQLException
   */
  private ResultMessage setPreparedStatementStringValue(
      PreparedStatement pst, Map<String, ImportCellDesc> map,
      Map<Integer, String> paras, String primaryKey) throws SQLException {
    Set<Integer> set = paras.keySet();
    for (int key : set) {
      String fieldName = paras.get(key);
      if (fieldName.equalsIgnoreCase("conf_ci_id")) // 资产关联ID
      {
        pst.setString(key, primaryKey);
        break;
      } else {

        Object cellValue = null;
        try {
          ImportCellDesc icdec=map.get(fieldName);
          if(icdec!=null)
          {
            cellValue = icdec.getFieldValue();
          }else
          {
            cellValue="";
          }   
        } catch (Exception e) {
          return ResultMessage.newInstance(false, "错误信息：数据项【"
              + fieldName
              + "】对应的Excel模板列为空，请检查Excel模板列是否与数据项一致或者是否存在重复数据项！");
        }
        String str = "";
        if (cellValue != null) {
          str = cellValue.toString().trim();
        }
        pst.setString(key, str);
      }
    }
    return ResultMessage.success();

  }

  /**
   * 导入自定义属性实体（资产）
   * 
   * @param importType
   *            导入类型 0：新增 1：更新
   * @param strSql
   *            导入语句
   * @param excelData
   *            导入数据Excel
   * @param dbEntity
   *            导入实体
   * @param paras
   *            参数列表
   * @param cusParas
   *            自定义参数列表
   * @throws SQLException
   */
  private ResultMessage importCustomFieldEntity(String importType,
      String strSql, ExcelData excelData, DbEntity dbEntity,
      List<Map<Integer, DbEntityField>> paras,
      List<Map<Integer, String>> lstcusParas, String categoryId,
      String categoryName) throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession(false);
    Connection con = sqlSession.getConnection();
    List<String> pkeyList=new ArrayList<String>();

    // 首先删除原来表中所有的数据
    if (importType.equalsIgnoreCase("0")) {
      if (dbEntity.getTableName().equalsIgnoreCase("conf_ci")) {
        String sqlWhere = "  and conf_category_id='" + categoryId + "'";
        // 删除明细表
        String delDetailSql = "delete from conf_ci_property where conf_ci_id in (select id from conf_ci where 1=1"
            + sqlWhere + ")";
        Statement statementDetail = con.createStatement();
        statementDetail.execute(delDetailSql);
        statementDetail.close();

        // 删除主表
        String delSql = "delete from " + dbEntity.getTableName()
            + " where 1=1 " + sqlWhere;
        Statement statement = con.createStatement();
        statement.execute(delSql);
        statement.close();
      }

    }

    String[] sqlList = strSql.split(";");
    List<PreparedStatement> psts = new ArrayList<PreparedStatement>();
    for (int i = 0; i < sqlList.length; i++) {
      psts.add(con.prepareStatement(sqlList[i]));
    }

    // 2、开始导入
    List<Map<String, ImportCellDesc>> list = excelData.getRepeatData();
    for (int i = 0; i < list.size(); i++) {
      Map<String, ImportCellDesc> map = list.get(i);
      String primaryKey = map.get("code").getFieldValue().toString();
      pkeyList.add(primaryKey);

      for (int j = 0; j < psts.size(); j++) {
        ResultMessage resultMessage = null;
        if (j < paras.size()) {

          resultMessage = setPreparedStatementValue(psts.get(j), map,
              paras.get(j), primaryKey, categoryId, categoryName);

        } else {
          resultMessage = setPreparedStatementStringValue(
              psts.get(j), map,
              lstcusParas.get(j - paras.size()), primaryKey);
        }
        if (!resultMessage.success) {
          return resultMessage;
        }

      }
      for (PreparedStatement pstnew : psts) {
        pstnew.addBatch();
      }
      for (PreparedStatement pstnew : psts) {
        pstnew.executeBatch();
      }

      con.commit();
    }

    // sqlSession.close();
    return ResultMessage.success(pkeyList);

  }

  /**
   * 根据excel与数据实体的映射关系找到对应的属性对象
   * 
   * @param excelColumnToEntityField
   * @param fields
   * @return
   */
  private DbEntityField getDbEntityField(
      ExcelColumnToEntityField excelColumnToEntityField,
      List<DbEntityField> fields) {
    DbEntityField dbEntityField = null;
    for (DbEntityField field : fields) {
      if (excelColumnToEntityField.getEntityField().equals(
          field.getName())) {
        dbEntityField = field;
        break;
      }
    }
    return dbEntityField;
  }

}
